---
sidebar_label: Tables basics
sidebar_position: 1
description: GraphQL over MS SQL Server tables in Hasura
keywords:
  - hasura
  - docs
  - ms sql server
  - schema
  - tables
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Thumbnail from '@site/src/components/Thumbnail';
import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

# MS SQL Server: Tables Basics

## Introduction

Adding tables allows you to define the GraphQL types of your schema including their corresponding fields.

## Creating tables {#ms-sql-server-create-tables}

Let's say we want to create two simple tables for `articles` and `authors` schema:

```sql
authors (
  id int PRIMARY KEY,
  name text
)

articles (
  id int PRIMARY KEY,
  title text,
  content text,
  rating int,
  author_id int
)
```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Open the Hasura Console and head to the `Data` tab and click on the button on the left side bar to open up an interface
to create tables.

For example, here is the schema for the `articles` table in this interface:

<Thumbnail src="/img/schema/create-table-graphql-mssql.png" alt="Schema for an article table" width="1100px" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  [Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
    following SQL statement to the `up.sql` file:

    ```sql
    CREATE TABLE articles(id int NOT NULL, title text NOT NULL, content text NOT NULL, rating int NOT NULL, author_id int NOT NULL, PRIMARY KEY (id));
    ```

2.  Add the following statement to the `down.sql` file in case you need to
    [roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

    ```sql
    DROP TABLE articles;
    ```

3.  Apply the migration by running:

    ```bash
    hasura migrate apply
    ```

</TabItem> 
<TabItem value="api" label="API">

You can create a table by making an API call to the
[schema_run_sql Metadata API](/api-reference/schema-api/run-sql.mdx#schema-run-sql):

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db-name>",
    "sql": "CREATE TABLE articles(id int NOT NULL, title text NOT NULL, content text NOT NULL, rating int NOT NULL, author_id int NOT NULL, PRIMARY KEY (id));"
  }
}
```

</TabItem>
</Tabs>

## Tracking tables

Tables can be present in the underlying MS SQL Server database without being exposed over the GraphQL API. In order to
expose a table over the GraphQL API, it needs to be **tracked**.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

When a table is created via the Hasura Console, it gets tracked by default.

You can track any existing tables in your database from the `Data -> Schema` page:

<Thumbnail src="/img/schema/schema-track-tables-mssql.png" alt="Track table" width="700px" />

</TabItem>
<TabItem value="cli" label="CLI">

1.  To track the table and expose it over the GraphQL API, edit the `tables.yaml` file in the `metadata` directory as
    follows:

    ```yaml {4-6}
    - table:
        schema: dbo
        name: authors
    - table:
        schema: dbo
        name: articles
    ```

2.  Apply the Metadata by running:

    ```bash
    hasura metadata apply
    ```

</TabItem>
<TabItem value="api" label="API">

To track the table and expose it over the GraphQL API, make the following API call to the
[mssql_track_table Metadata API](/api-reference/metadata-api/table-view.mdx#mssql-track-table):

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_track_table",
  "args": {
    "source": "<db_name>",
    "table": "authors"
  }
}
```

</TabItem>
</Tabs>

## Generated GraphQL schema types

As soon as a table is created and tracked, the corresponding GraphQL schema types and query fields will be automatically
generated.

The following object type is generated for the `articles` table we just created and tracked:

```graphql
# Object type
type Articles {
  id: Int
  title: String
  content: String
  rating: Int
  author_id: Int
}
```

Let's analyze the above type:

- `Articles` is the name of the type
- `id`, `title`, `content`, `rating` and `author_id` are fields of the `Articles` type
- `Int` and `String` are types that fields can have

The following query fields are generated for the `articles` table we just created and tracked:

```graphql
# Query field
articles (
  where: articles_bool_exp
  limit: Int
  offset: Int
  order_by: [articles_order_by!]
): [articles!]!
```

<!-- TODO: MSSQL_UNSUPPORTED

  # insert/upsert mutation field
  insert_articles (
    objects: [articles_insert_input!]!
    on_conflict: articles_on_conflict
  ): articles_mutation_response

  # update mutation field
  update_articles (
    where: articles_bool_exp!
    _inc: articles_inc_input
    _set: articles_set_input
  ): articles_mutation_response

  # delete mutation field
  delete_articles (
    where: articles_bool_exp!
  ): articles_mutation_response -->

These auto-generated fields will allow you to query data in our table.

See the [query](/api-reference/graphql-api/query.mdx) API reference for the full specifications.

## Try out basic GraphQL requests

At this point, you should be able to try out basic GraphQL queries on the newly created tables from the `API` tab in the
console. _(You may want to add some sample data into the tables first)_

- Query all rows in the `articles` table:

<GraphiQLIDE
  query={`query {
   articles {
    id
    title
    author_id
  }
}`}
  response={`{
  "data": {
    "articles": [
      {
        "id": 1,
        "title": "sit amet",
        "author_id": 4
      },
      {
        "id": 2,
        "title": "a nibh",
        "author_id": 2
      },
      {
        "id": 3,
        "title": "amet justo morbi",
        "author_id": 4
      },
      {
        "id": 4,
        "title": "vestibulum ac est",
        "author_id": 5
      }
    ]
  }
}`}
/>

<!--

TODO: MSSQL_UNSUPPORTED

   Insert data in the ``author`` table:

    .. graphiql::
      :view_only:
      :query:
        mutation add_author {
          insert_author(
            objects: [
              { name: "Jane" }
            ]
          ) {
              affected_rows
              returning {
                id
                name
              }
            }
        }
      :response:
        {
          "data": {
            "insert_author": {
              "affected_rows": 1,
              "returning": [
                {
                  "id": 11,
                  "name": "Jane"
                }
              ]
            }
          }
        }

 -->
